package com.personal.test.service.serviceImpl;


import com.fasterxml.jackson.databind.exc.InvalidFormatException;
import com.personal.test.common.domin.R;
import com.personal.test.service.ImportService;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;

/**
 * @author linyh
 * @version 1.0
 * @email 1503386669@qq.com
 * @date 2020/10/20 17:24
 */
@Service
public class ImportServiceImpl implements ImportService {

    @Override
    public R readExcelFile(MultipartFile file) {
        // 读取Excel里面的信息
        Workbook wb = null;
        try {
            wb = create(file.getInputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
        // 得到第一个shell
        Sheet sheet = wb.getSheetAt(0);
        // 得到Excel的行数
        int rows = sheet.getPhysicalNumberOfRows();
        // 得到Excel的列数
        int columns = sheet.getRow(0).getPhysicalNumberOfCells();
        // 遍历行(从1开始，去除Excel表头数据)
        for (int i = 1; i < rows; i++) {
            Row row = sheet.getRow(i);
            // 在某行的基础下，遍历该行所有列
            for (int i1 = 0; i1 < columns; i1++) {
                // 获取单元格
                Cell cell = row.getCell(i1);
                if(null != cell){
                    // 设置单元格类型，方便取值
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    // 获取单元格内容
                    String value = String.valueOf(cell.getRichStringCellValue());
                    // todo:将内容赋值到某个实体类进入sql插入操作（数据封装，再插入即可）
                }
            }
        }
        return R.success();
    }

    /**
     * 对HSSFWorkbook/XSSFWorkbook兼容处理
     * @param inp
     * @return
     * @throws IOException
     * @throws InvalidFormatException
     */
    public static Workbook create(InputStream inp) throws IOException, InvalidFormatException {
        if (!inp.markSupported()) {
            inp = new PushbackInputStream(inp, 8);
        }
        if (POIFSFileSystem.hasPOIFSHeader(inp)) {
            return new HSSFWorkbook(inp);
        }
        if (POIXMLDocument.hasOOXMLHeader(inp)) {
            try {
                return new XSSFWorkbook(OPCPackage.open(inp));
            } catch (org.apache.poi.openxml4j.exceptions.InvalidFormatException e) {
                e.printStackTrace();
            }
        }
        throw new IllegalArgumentException("你的excel版本目前poi解析不了");
    }
}
